# _*_ coding: utf-8 _*_
from odoo import models, fields, api, tools


class StockMonthBalanceReport(models.Model):
    _name = "stock.month.balance.report"
    _auto = False
    _description = u"进销存汇总表"

    month = fields.Many2one("resource.calendar", string=u"月份")
    prod_lot_id = fields.Many2one('stock.production.lot', u'批次/序列号')
    product_id = fields.Many2one('product.product', u'产品', readonly=True, related='prod_lot_id.product_id', store=True)
    product_uom_id = fields.Many2one('product.uom', u'单位', readonly=True, related="prod_lot_id.product_uom_id")
    location_id = fields.Many2one('stock.location', u'库位')
    product_qty_in = fields.Float(u'入数量')
    product_qty_out = fields.Float(u'出数量')
    product_qty_start = fields.Float(string=u"期初数量")
    product_qty_end = fields.Float(u'期末结存')

    def init(self):
        tools.drop_view_if_exists(self._cr, 'stock_month_balance_report')
        self._cr.execute("""
            CREATE VIEW stock_month_balance_report AS (
                select * from (select row_number() over() as id, product_id as product_id,location_id as location_id,month as month,
sum(product_qty_start) as product_qty_start,sum(product_qty_in) as  product_qty_in,
sum(product_qty_out) as product_qty_out, sum(product_qty_start)+sum(product_qty_in)-sum(product_qty_out) as product_qty_end,prod_lot_id as prod_lot_id from 
(select row_number() over() as id,  product_id,location_id,month,0 as product_qty_start,product_qty_in,product_qty_out,
 coalesce(prod_lot_id,0) as prod_lot_id from stock_inout_line
union
select row_number() over() as id, product_id,location_id,r.next_month_id,product_qty_end,0,0,coalesce(prod_lot_id,0) from stock_month_balance b 
 join resource_calendar r on r.id = b.month) s group by product_id,location_id,prod_lot_id,month) a
where product_qty_in<>0 or product_qty_start<>0 or product_qty_out<>0
            )""")
